Practicing File Handling, Modules, and Database Connectivity Questions

Course: Python Training

Topic: Practicing File Handling, Modules, and Database Connectivity Questions

Course Link: Python Training Course – JustAcademy

Demo Link: Register for a Course Demo

Website: Visit JustAcademy

1. Introduction

File handling, modules, and database connectivity are essential Python skills used to build applications that store information, organize reusable code, and communicate with databases. File handling allows programs to read and write information in files. Modules help divide programs into reusable components. Database connectivity enables Python applications to store, retrieve, update, and delete structured records.

Practicing questions in these areas helps learners understand practical programming tasks, prepare for technical interviews, and develop applications that work with persistent data.

Learning Objectives

• Understand file operations and file-opening modes.
• Read, write, append, and process text and binary files.
• Use context managers to manage resources safely.
• Handle file-related exceptions and common errors.
• Create and import custom Python modules and packages.
• Understand module execution and the if __name__ == "__main__": pattern.
• Connect Python applications to SQLite databases.
• Execute SQL queries using parameterized statements.
• Manage transactions, cursors, connections, and database errors.
• Practice common interview questions and mini-project exercises.

2. Understanding File Handling in Python

File handling allows a program to access data stored outside its running process. Python provides the built-in open() function to open files and return file objects that support reading, writing, and other operations.

Basic Syntax

file = open("example.txt", "r", encoding="utf-8")
content = file.read()
file.close()

Although this syntax works, manually closing files can be forgotten when an exception occurs. The recommended approach is to use a with statement, which closes the file automatically when the block ends.

with open("example.txt", "r", encoding="utf-8") as file:
    content = file.read()

print(content)

Common File Modes

3. Reading Files

Python provides several methods for reading file content. The appropriate method depends on file size and the way the data needs to be processed.

Example: Reading the Entire File

with open("notes.txt", "r", encoding="utf-8") as file:
    content = file.read()

print(content)

Example: Reading a File Line by Line

with open("notes.txt", "r", encoding="utf-8") as file:
    for line_number, line in enumerate(file, start=1):
        print(f"{line_number}: {line.rstrip()}")

Iterating over a file avoids loading every line into memory at once. This is usually preferable for large logs, reports, and data files.

Practice Question 1: Count the Number of Lines in a File

Problem: Write a function that counts how many lines are present in a text file.

def count_lines(file_path):
    with open(file_path, "r", encoding="utf-8") as file:
        return sum(1 for _ in file)

print(count_lines("notes.txt"))

Explanation: The generator expression produces one value for each line, and sum() counts them. This implementation counts lines as they are read, including blank lines. An empty file returns zero.

4. Writing and Appending to Files

Writing replaces existing file content when the file is opened in w mode. Appending adds new content to the end of the file without removing its existing content.

Example: Writing Text

with open("report.txt", "w", encoding="utf-8") as file:
    file.write("Python File Handling\n")
    file.write("Writing data to a file.\n")

Example: Appending Text

with open("report.txt", "a", encoding="utf-8") as file:
    file.write("This line was appended later.\n")

Practice Question 2: Save User Input to a File

Problem: Accept a message from the user and append it to a file.

def save_message(message, file_path="messages.txt"):
    with open(file_path, "a", encoding="utf-8") as file:
        file.write(message + "\n")

message = input("Enter a message: ")
save_message(message)
print("Message saved successfully.")

Explanation: Append mode preserves earlier messages. In a production application, consider validating the input, controlling the file location, and handling errors such as permission failures.

5. Working with File Paths and Directories

The os and pathlib modules help work with file paths, directories, and file metadata. For most new Python projects, pathlib.Path provides a convenient object-oriented interface for paths.

from pathlib import Path

file_path = Path("data") / "records.txt"

print("File exists:", file_path.exists())
print("Is a file:", file_path.is_file())
print("Parent directory:", file_path.parent)

Creating a Directory and File

from pathlib import Path

folder = Path("data")
folder.mkdir(parents=True, exist_ok=True)

file_path = folder / "welcome.txt"
file_path.write_text("Welcome to Python.", encoding="utf-8")

content = file_path.read_text(encoding="utf-8")
print(content)

Practice Question 3: Check Whether a File Exists

from pathlib import Path

def file_exists(file_name):
    return Path(file_name).is_file()

print(file_exists("welcome.txt"))

Explanation: is_file() returns True when the path points to an existing regular file. It returns False for a missing path or a directory.

6. Exception Handling in File Operations

File operations can fail for several reasons, including missing files, insufficient permissions, invalid paths, encoding problems, and storage errors. Exception handling allows applications to respond to expected failures rather than terminating unexpectedly.

Common File Exceptions

Example: Safe File Reading

def read_file(file_path):
    try:
        with open(file_path, "r", encoding="utf-8") as file:
            return file.read()
    except FileNotFoundError:
        print("The requested file was not found.")
    except PermissionError:
        print("Permission denied while reading the file.")
    except UnicodeDecodeError:
        print("The file is not valid UTF-8 text.")
    except OSError as error:
        print(f"File operation failed: {error}")

    return None

content = read_file("input.txt")
if content is not None:
    print(content)

Best practice: Catch specific exceptions when the application can respond meaningfully. Avoid silently ignoring errors or catching every exception without recording useful information.

7. Working with CSV and JSON Files

Text files are useful for simple records, while CSV and JSON formats support structured data exchange. Python includes the csv and json modules in its standard library.

Reading a CSV File

import csv

with open("students.csv", "r", newline="", encoding="utf-8") as file:
    reader = csv.DictReader(file)

    for row in reader:
        print(row["name"], row["marks"])

For this example, the CSV file should contain headers such as name,marks. Using DictReader makes each row available as a dictionary keyed by the column names.

Writing a CSV File

import csv

students = [
    {"name": "Aarav", "marks": 85},
    {"name": "Meera", "marks": 92}
]

with open("students.csv", "w", newline="", encoding="utf-8") as file:
    fieldnames = ["name", "marks"]
    writer = csv.DictWriter(file, fieldnames=fieldnames)

    writer.writeheader()
    writer.writerows(students)

Writing and Reading JSON

import json

student = {
    "name": "Meera",
    "marks": 92,
    "subjects": ["Python", "SQL"]
}

with open("student.json", "w", encoding="utf-8") as file:
    json.dump(student, file, indent=4)

with open("student.json", "r", encoding="utf-8") as file:
    loaded_student = json.load(file)

print(loaded_student["name"])

JSON is useful for configuration files and nested data structures. CSV is often convenient for tabular data. Neither format replaces a database when an application needs reliable concurrent updates, relationships, constraints, and complex queries.

8. Understanding Python Modules

A module is a Python file containing functions, classes, constants, or executable statements. Modules help organize code into logical units, improve reuse, and make projects easier to test and maintain.

Example: Creating a Custom Module

Create a file named calculator.py:

def add(a, b):
    return a + b

def subtract(a, b):
    return a - b

PI = 3.14159

Create another file named main.py in the same directory:

import calculator

print(calculator.add(10, 5))
print(calculator.subtract(10, 5))
print(calculator.PI)

When main.py imports calculator, it can access the module's public names through the module object.

Different Import Styles

import math
print(math.sqrt(25))

from math import sqrt
print(sqrt(36))

import math as mathematics
print(mathematics.factorial(5))

Using import module keeps the origin of a name clear. Avoid wildcard imports such as from module import * in most application code because they can make it difficult to determine where names come from.

9. The __name__ Variable and Script Entry Points

Python sets the special variable __name__ according to how a module is being used. When a file is executed directly, its value is usually "__main__". When imported, the value is normally the module's import name.

def main():
    print("Application started.")

if __name__ == "__main__":
    main()

This pattern allows a module to provide reusable functions while also supporting direct execution for demonstrations, command-line tools, or testing.

Practice Question 4: Create a Reusable Utility Module

Problem: Create a module that provides functions to calculate the square and cube of a number, then import and use those functions from another file.

Suggested solution: Put the functions in math_utils.py and import the module in main.py.

# math_utils.py
def square(number):
    return number ** 2

def cube(number):
    return number ** 3

# main.py
import math_utils

print(math_utils.square(4))
print(math_utils.cube(3))

Expected output: 16 and 27.

10. Python Packages and Project Organization

A package organizes related modules into a directory hierarchy. Packages are useful when a project grows beyond a few files and needs clear separation of responsibilities.

Example Project Structure

python_app/
    main.py
    file_utils.py
    math_utils.py
    storage/
        __init__.py
        json_store.py
        csv_store.py
    tests/
        test_math_utils.py
    README.md

• main.py contains the application's entry point.
• file_utils.py contains reusable file operations.
• math_utils.py contains mathematical helper functions.
• storage/ groups modules for persistence tasks.
• tests/ contains automated tests.
• README.md explains setup, usage, and project details.

An __init__.py file can mark a directory as a regular package and can define package-level behavior. Python also supports namespace packages without this file in appropriate situations.

11. Introduction to Database Connectivity

Database connectivity allows Python programs to communicate with a database management system. Applications can use databases to store structured records, retrieve data through queries, update existing information, and delete records when necessary.

Important Database Terms

Common Database Operations: CRUD

• Create: Insert a new record.
• Read: Retrieve existing records.
• Update: Modify stored records.
• Delete: Remove records.

12. Connecting Python to SQLite

SQLite is a lightweight relational database that stores data in a local database file. Python includes the sqlite3 module, so no separate Python package is required for basic SQLite connectivity.

Establishing a Connection

import sqlite3

connection = sqlite3.connect("school.db")

try:
    print("Connected to SQLite database.")
finally:
    connection.close()

If the database file does not exist, sqlite3.connect() normally creates it. The connection should be closed when it is no longer needed. In longer applications, a dedicated function or context manager pattern can help manage connection and transaction lifecycles.

Creating a Table

import sqlite3

connection = sqlite3.connect("school.db")

try:
    connection.execute("""
        CREATE TABLE IF NOT EXISTS students (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            marks REAL NOT NULL
        )
    """)
    connection.commit()
finally:
    connection.close()

The CREATE TABLE IF NOT EXISTS statement creates the table only when it is not already present. The NOT NULL constraints prevent those columns from storing SQL NULL values.

13. Inserting Records into a Database

SQL's INSERT statement adds new records to a table. Python's SQLite API supports parameterized queries, which safely separates SQL instructions from data values.

Example: Insert One Student

import sqlite3

connection = sqlite3.connect("school.db")

try:
    connection.execute(
        "INSERT INTO students (name, marks) VALUES (?, ?)",
        ("Aarav", 85)
    )
    connection.commit()
    print("Student inserted successfully.")
finally:
    connection.close()

The question marks are SQLite parameter placeholders. The values are passed separately as a tuple. This is safer than building SQL statements through string concatenation.

Practice Question 5: Insert Multiple Records

Problem: Insert several student records efficiently.

import sqlite3

students = [
    ("Meera", 92),
    ("Kabir", 78),
    ("Aarav", 85)
]

connection = sqlite3.connect("school.db")

try:
    connection.executemany(
        "INSERT INTO students (name, marks) VALUES (?, ?)",
        students
    )
    connection.commit()
finally:
    connection.close()

Explanation: executemany() executes a parameterized statement for each supplied parameter set. It is useful for inserting many rows with the same SQL statement.

14. Retrieving Records from a Database

The SQL SELECT statement retrieves data from one or more tables. Python's SQLite cursor or connection methods can execute the query and return the results.

Example: Fetch All Records

import sqlite3

connection = sqlite3.connect("school.db")

try:
    cursor = connection.execute(
        "SELECT id, name, marks FROM students ORDER BY name"
    )

    for row in cursor.fetchall():
        print(row)
finally:
    connection.close()

Common Fetch Methods

For large result sets, iterate over the cursor or fetch rows in batches instead of calling fetchall(), which may consume substantial memory.

Practice Question 6: Find Students Above a Marks Threshold

import sqlite3

threshold = 80
connection = sqlite3.connect("school.db")

try:
    cursor = connection.execute(
        "SELECT name, marks FROM students WHERE marks > ?",
        (threshold,)
    )

    for name, marks in cursor:
        print(name, marks)
finally:
    connection.close()

Explanation: The query retrieves students whose marks are greater than the supplied threshold. The trailing comma in (threshold,) creates a one-element tuple.

15. Updating and Deleting Database Records

The UPDATE statement modifies existing records. The DELETE statement removes records. Always consider the WHERE clause carefully because omitting it can affect every row in the table.

Updating a Student's Marks

import sqlite3

connection = sqlite3.connect("school.db")

try:
    cursor = connection.execute(
        "UPDATE students SET marks = ? WHERE name = ?",
        (95, "Meera")
    )
    connection.commit()
    print("Rows updated:", cursor.rowcount)
finally:
    connection.close()

Deleting a Student by ID

import sqlite3

student_id = 3
connection = sqlite3.connect("school.db")

try:
    cursor = connection.execute(
        "DELETE FROM students WHERE id = ?",
        (student_id,)
    )
    connection.commit()
    print("Rows deleted:", cursor.rowcount)
finally:
    connection.close()

In applications where a record must be unique, using a primary key is generally more reliable than matching a name that may appear more than once.

16. Parameterized Queries and SQL Injection Prevention

SQL injection occurs when untrusted input is incorporated into SQL syntax in a way that changes the meaning of a query. Parameterized queries keep data values separate from SQL code and are the recommended approach for passing values into SQL statements.

Unsafe Approach

# Avoid constructing SQL using untrusted input.
username = input("Username: ")
query = "SELECT * FROM users WHERE username = '" + username + "'"

Safe Parameterized Approach

import sqlite3

username = input("Username: ")
connection = sqlite3.connect("app.db")

try:
    cursor = connection.execute(
        "SELECT id, username FROM users WHERE username = ?",
        (username,)
    )
    rows = cursor.fetchall()
    print(rows)
finally:
    connection.close()

Use the placeholder syntax supported by the database driver. SQLite uses question marks in this example; other database drivers may use different placeholder formats.

Placeholders are intended for values, not table names, column names, or SQL keywords. If a query needs a dynamic identifier, select it from a strict allowlist rather than inserting arbitrary user-provided text.

17. Database Transactions and Error Handling

A transaction groups related database operations so they can be committed together or rolled back when a failure occurs. Transactions help prevent partially completed updates.

Example: Transaction with Rollback

import sqlite3

connection = sqlite3.connect("school.db")

try:
    connection.execute(
        "INSERT INTO students (name, marks) VALUES (?, ?)",
        ("Riya", 88)
    )
    connection.execute(
        "INSERT INTO students (name, marks) VALUES (?, ?)",
        ("Dev", 91)
    )
    connection.commit()
except sqlite3.Error as error:
    connection.rollback()
    print("Database operation failed:", error)
finally:
    connection.close()

If a database operation raises a SQLite error, the example rolls back the transaction and then closes the connection. In a production application, log appropriate diagnostic information and avoid exposing confidential details in user-facing error messages.

Important Transaction Terms

• Commit: Finalizes changes in the current transaction.
• Rollback: Reverts uncommitted changes in the current transaction.
• Atomicity: A transaction's operations succeed as a unit or are rolled back as a unit.
• Consistency: Transactions should preserve defined data rules and constraints.
• Isolation: Concurrent transactions are controlled according to the database's isolation behavior.
• Durability: Committed changes are intended to persist according to the database's guarantees.

18. Using SQLite Context Managers Correctly

SQLite connection objects support the context manager protocol for transaction management. A with connection: block commits a successful transaction or rolls it back if an exception occurs, but it does not itself close the connection.

import sqlite3

connection = sqlite3.connect("school.db")

try:
    with connection:
        connection.execute(
            "INSERT INTO students (name, marks) VALUES (?, ?)",
            ("Nisha", 89)
        )
finally:
    connection.close()

This pattern makes the transaction boundary explicit and ensures the connection is closed in the finally block. For applications with many database operations, consider centralizing connection management to avoid repeated setup and cleanup code.

19. Connecting to Other Database Systems

SQLite is convenient for learning and small applications. Production systems may use database servers such as MySQL or PostgreSQL. Python typically connects to those systems through a compatible database driver or an abstraction library.

Install only the driver appropriate for the database you use. Store database credentials in environment variables or a managed secrets system rather than hard-coding passwords into source files.

Example: Reading an Environment Variable

import os

database_url = os.getenv("DATABASE_URL")

if not database_url:
    raise RuntimeError("DATABASE_URL is not configured")

print("Database configuration is available.")

This example checks that a configuration value exists without printing its contents. The method used to connect depends on the database driver and the expected format of the configuration value.

20. Frequently Asked Interview Questions

Q1. What is the difference between read(), readline(), and readlines()?

read() reads all remaining content or a specified amount. readline() reads one line. readlines() returns the remaining lines in a list. For large files, iterating over the file object is usually more memory-efficient.

Q2. Why should we use the with statement when opening files?

The with statement manages the file resource and ensures that it is closed when the block exits, including when an exception occurs.

Q3. What is the difference between write mode and append mode?

Write mode (w) truncates an existing file before writing. Append mode (a) adds new data at the end and preserves existing content.

Q4. What is a Python module?

A module is a Python file that can contain functions, classes, constants, and other statements. Modules let developers divide code into reusable and maintainable units.

Q5. What is the difference between a module and a package?

A module is typically one Python file. A package organizes related modules under a common package namespace, usually using a directory structure.

Q6. Why do we use if __name__ == "__main__":?

It allows code to run when the file is executed directly while preventing that entry-point code from running merely because the module is imported.

Q7. What is database connectivity?

Database connectivity is the process by which an application establishes a connection to a database and executes operations to read or modify data.

Q8. What is a cursor?

A cursor is a database API object used to execute SQL statements and access query results. Some database APIs also allow queries to be executed directly through the connection object.

Q9. Why should SQL queries be parameterized?

Parameterized queries separate SQL instructions from input values, reduce SQL injection risk, and allow the database driver to handle values correctly.

Q10. What is the difference between commit() and rollback()?

commit() finalizes a transaction's changes. rollback() reverses changes that have not been committed within the transaction.

Q11. What is a primary key?

A primary key uniquely identifies each row in a table. It must satisfy the database's primary-key constraints and is commonly used to reference a record.

Q12. What is the difference between fetchone() and fetchall()?

fetchone() retrieves the next row, or returns None when no row remains. fetchall() retrieves all remaining rows into a list.

Q13. How can SQL injection be prevented?

Use parameterized queries for values, validate inputs according to business requirements, restrict database permissions, and avoid building SQL statements through untrusted string concatenation.

Q14. Why is it important to close database connections?

Connections consume resources. Closing them when they are no longer needed helps prevent resource leaks and supports reliable application behavior.

21. Practical Coding Exercises

Try these questions independently before reviewing your solution. Include appropriate exception handling and test your code using both valid and invalid inputs.

1. Write a program that reads a text file and counts its words.
2. Copy the contents of one text file to another.
3. Append a timestamped log message to a file.
4. Read a CSV file and calculate the average of a numeric column.
5. Store a Python dictionary in JSON format and load it back.
6. Create a module with functions for calculating a circle's area and circumference.
7. Create a package containing separate modules for file utilities and data validation.
8. Connect to SQLite and create a table for employee records.
9. Insert multiple records into the employee table using parameterized queries.
10. Retrieve employees whose salaries exceed a given threshold.
11. Update an employee's salary using the employee ID.
12. Delete a record using a primary key and report the number of affected rows.
13. Write a transaction that inserts two related records and rolls back if an operation fails.
14. Write unit tests for a function that reads and validates a data file.
15. Build a small application that imports custom modules and saves information to a database.

22. Mini Project: Student Record Management System

Create a command-line Student Record Management System that combines file handling, custom modules, and SQLite database connectivity. The application should store student details persistently and provide options for adding, viewing, updating, and deleting records.

Project Requirements

• Allow users to add a student with a name, email, and marks.
• Validate required fields before saving information.
• Store student records in an SQLite database.
• Display all student records in a readable format.
• Search for a student using a unique ID.
• Update a student's marks and contact information.
• Delete a student after confirming the operation.
• Export student records to a CSV file.
• Handle database and file errors gracefully.
• Use parameterized SQL statements for all user-supplied values.
• Organize the application into reusable modules.
• Write automated tests for core functions and document how to run the project.

Suggested Project Structure

student_manager/
    main.py
    database.py
    student_service.py
    file_utils.py
    validators.py
    tests/
        test_validators.py
        test_student_service.py
    README.md

Suggested Development Steps

1. Define the student table and its constraints.
2. Create database connection and initialization functions.
3. Write CRUD functions using parameterized queries.
4. Implement validation and error handling.
5. Create CSV export functionality.
6. Build a command-line menu that calls the service functions.
7. Write tests for valid input, invalid input, missing records, and database failures.
8. Document setup instructions, features, and sample usage.

23. Best Practices and Troubleshooting Checklist

File Handling

• Use with open(...) for reliable file resource management.
• Specify an encoding such as UTF-8 when working with text files.
• Use pathlib for clear and portable path operations.
• Choose the correct mode and avoid unintentionally truncating important files.
• Use line-by-line processing for large text files.
• Handle missing files, permission errors, and decoding failures appropriately.

Modules and Packages

• Give modules descriptive names that do not shadow standard-library modules.
• Separate responsibilities into focused functions and modules.
• Avoid circular imports when designing module relationships.
• Use explicit imports and avoid unnecessary wildcard imports.
• Keep application entry-point logic under the main guard.
• Document public functions and write tests for reusable code.

Database Connectivity

• Use parameterized queries instead of concatenating user input into SQL.
• Commit successful changes and roll back failed transactions when appropriate.
• Close database connections reliably.
• Use primary keys and appropriate constraints to protect data integrity.
• Handle database exceptions and log useful diagnostic information.
• Never store passwords directly in source code or expose sensitive credentials in logs.
• Use transactions for operations that must succeed or fail together.
• Test queries against empty results, duplicate records, and invalid inputs.

24. Quick Revision Summary

• File handling: Use open(), suitable file modes, and context managers.
• Reading: Understand read(), readline(), readlines(), and file iteration.
• Writing: Know the difference between w, a, and x modes.
• Structured files: Use csv for tabular data and json for JSON-compatible structures.
• Paths: Use pathlib for portable path management.
• Modules: Create reusable Python files and import them appropriately.
• Packages: Group related modules into a maintainable project structure.
• Entry point: Use the main guard to control direct script execution.
• Database: Understand connections, cursors, tables, rows, and SQL statements.
• CRUD: Practice inserting, retrieving, updating, and deleting records.
• Security: Use parameterized queries to reduce SQL injection risk.
• Transactions: Understand commit(), rollback(), and transaction boundaries.
• Quality: Test edge cases, handle errors, and document application behavior.

25. Conclusion

Practicing file handling, modules, and database connectivity strengthens the practical skills needed to develop reliable Python applications. File operations help manage persistent files, modules encourage reusable code, and database connectivity supports structured storage and retrieval of application data.

To reinforce these concepts, practice reading and writing different file formats, create your own reusable modules, execute parameterized SQL queries, and build the Student Record Management System. Focus on clean structure, appropriate error handling, secure query design, and automated testing.

Learn more through the Python Training Course at JustAcademy, register for a course demo, or visit the JustAcademy website.